<?php

namespace App\Http\Controllers;

use App\Models\AccessLog;
use Illuminate\Http\Request;
use App\Models\CommentDetail;
use App\Models\User;
use App\Models\Forum;
use App\Models\Topic;
use App\Models\Comment;
use GuzzleHttp\Client;
use App\Models\HuisuoJishi;
use Illuminate\Support\Facades\Auth;

class HomeController extends Controller
{
    /**
     * Create a new controller instance.
     *
     * @return void
     */
    public function __construct()
    {
//        $this->middleware('auth');
    }

    /**
     * Show the application dashboard.
     *
     * @return \Illuminate\Http\Response
     */
    public function index()
    {
        return view('home');
    }

    public function welcome()
    {
        return view('welcome');
    }

    public function test(Request $request)
    {
        $tableName = (new AccessLog())->getTable();
        $begin = date('Y-m-d', strtotime('yesterday'));
        $end = "$begin 23:59:59";
        //第一列，全部的登录用户
        $loginUserCountsGrouped = AccessLog::query()->selectRaw('province, count(*) as counts')->fromSub(function ($query) use ($tableName) {
            $query->select(['province', 'admin_id'])->from($tableName)->groupBy(['province', 'admin_id']);
        }, 'a')->groupBy('province')->get()->groupBy('province');

        $loginUserCountsTotal = AccessLog::query()->fromSub(function ($query) use ($tableName) {
            $query->select(['admin_id'])->from($tableName)->groupBy(['admin_id']);
        }, 'a')->count();


        //第二列，昨日的登录用户
        $loginUserCountsGroupedYesterday = AccessLog::query()->selectRaw('province, count(*) as counts')->fromSub(function ($query) use ($tableName, $begin, $end) {
            $query->select(['province', 'admin_id'])->from($tableName)
                ->where('created_at', '>=', $begin)->where('created_at', '<=', $end)->groupBy(['province', 'admin_id']);
        }, 'a')->groupBy('province')->get()->groupBy('province');

        $loginUserCountsTotalYesterday = AccessLog::query()->fromSub(function ($query) use ($tableName, $begin, $end) {
            $query->select(['admin_id'])->from($tableName)
                ->where('created_at', '>=', $begin)->where('created_at', '<=', $end)->groupBy(['admin_id']);
        }, 'a')->count();


        //第三列，昨日的登录次数
        $loginTimesGroupedYesterday = AccessLog::query()->selectRaw('province, count(*) as counts')
            ->where('created_at', '>=', $begin)->where('created_at', '<=', $end)
            ->groupBy('province')->get()->groupBy('province');

        $loginTimesTotalYesterday = AccessLog::query()->where('created_at', '>=', $begin)
            ->where('created_at', '<=', $end)->count();



        //全部的省份数据
        $provinceCollection = AccessLog::query()->select(['province'])->where('province', '!=', '')->groupBy('province')->get();


        $fileName = sprintf("用户统计-%s——%s.csv", $begin, preg_replace('/[\s]+/', '_', $end));
        header('Access-Control-Allow-Origin: *');//跨域支持。
        header('Content-Description: File Transfer');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment; filename="'. $fileName .'"');
        header('Expires: 0');
        header('Cache-Control: must-revalidate');
        header('Pragma: public');
        $fp = fopen('php://output', 'a');//打开output流
        $header = ['省份', '当前注册用户', '昨日登录用户', '昨日登录次数'];
        mb_convert_variables('GBK', 'UTF-8', $header);
        fputcsv($fp, $header);
//
//        //合计
        $row = ['合计'];
        $row[] = $loginUserCountsTotal; //注册用户数
        $row[] = $loginUserCountsTotalYesterday; // 登录用户数
        $row[] = $loginTimesTotalYesterday; //登录次数
        mb_convert_variables('GBK', 'UTF-8', $row);
        fputcsv($fp, $row);

        //各省份
        foreach ($provinceCollection as $province) {
            $name = $province->province;
            $row = [$name];
            $row[] = $loginUserCountsGrouped->has($name) ? $loginUserCountsGrouped->get($name)->first()->counts : 0; //注册用户数
            $row[] = $loginUserCountsGroupedYesterday->has($name) ? $loginUserCountsGroupedYesterday->get($name)->first()->counts : 0; // 登录用户数
            $row[] = $loginTimesGroupedYesterday->has($name) ? $loginTimesGroupedYesterday->get($name)->first()->counts : 0; //登录次数
            mb_convert_variables('GBK', 'UTF-8', $row);
            fputcsv($fp, $row);
        }
//        其他
        $row = ['其他'];
        $name = '';
        $row[] = $loginUserCountsGrouped->has($name) ? $loginUserCountsGrouped->get($name)->first()->counts : 0; //注册用户数
        $row[] = $loginUserCountsGroupedYesterday->has($name) ? $loginUserCountsGroupedYesterday->get($name)->first()->counts : 0; // 登录用户数
        $row[] = $loginTimesGroupedYesterday->has($name) ? $loginTimesGroupedYesterday->get($name)->first()->counts : 0; //登录次数
        mb_convert_variables('GBK', 'UTF-8', $row);
        fputcsv($fp, $row);

        ob_flush();
        flush();//必须同时使用 ob_flush() 和flush() 函数来刷新输出缓冲。

        exit;
    }

    public function test2()
    {
        dd(auth_user());
    }
}
